import json

import pandas as pd

# 指定文件路径
file_path = r'D:\GJ\project\SiChuan\隧道起终点经纬度修正.xlsx'
# 读取整个 Excel 文件的所有 sheet
xls = pd.ExcelFile(file_path)
# 查看所有 sheet 名称
print("Sheet names:", xls.sheet_names)
# 选择一个 sheet 读取为 DataFrame
df = pd.read_excel(file_path, usecols=[0, 5, 6, 7, 10, 11], sheet_name='Sheet1')
# 显示前几行数据
# print(df.head())

# # 假设 df 是你的 DataFrame
# reversed_df = df.iloc[::-1]
# print(reversed_df.head())

# 自定义列名映射关系
new_columns = {
    '摄像机IP': 'cameraIP',
    '桩号': 'PileNumber',
    '经度-新': 'longitude',
    '纬度-新': 'latitude',
    '方向': 'direction',
    '设备类型': 'deviceType'
}
# 修改列名
df = df.rename(columns=new_columns)

# 按df的第六列分组
groups = df.groupby(df.iloc[:, 5])
# 或者保存为字典供后续使用
classified_dict = {name: group for name, group in groups}
# 示例：查看某一个类别的前几行
print(classified_dict["枪机"].head())
group_a001 = classified_dict["枪机"]
sub_groups = group_a001.groupby(group_a001.iloc[:, 4])
# for sub_name, sub_df in sub_groups:
#     print(f"\n--- 子分组: {sub_name} ---")
#     print(sub_df.head())
sub_dict = {name: df for name, df in sub_groups}
all_devices = {"up": {}, "down": {}, "kakou": {}}
up_data = sub_dict["上行"]
down_data = sub_dict["下行"]
kakou_data = classified_dict["卡口摄像机"]
print(type(kakou_data), type(up_data), type(down_data))

# 填充空值为 ''
# 假设你只想对 '列名1', '列名2' 这些列填充空字符串
columns_to_fill = ['direction']
kakou_data[columns_to_fill] = kakou_data[columns_to_fill].fillna('')
up_data[columns_to_fill] = up_data[columns_to_fill].fillna('')
down_data[columns_to_fill] = down_data[columns_to_fill].fillna('')

kakou_data_result = kakou_data.set_index('PileNumber').to_dict(orient='index')
up_data_result = up_data.set_index('PileNumber').to_dict(orient='index')
down_data_result = down_data.set_index('PileNumber').to_dict(orient='index')
print(up_data_result)
kakou_list = list(kakou_data_result.keys())[::-1]
up_list = list(up_data_result.keys())[::-1]
down_list = list(down_data_result.keys())[::-1]
print(len(kakou_list), len(up_list), len(down_list))
print(kakou_list)
print(up_list)
print(down_list)

save_data = {
    "list_info": {
        "kakou": kakou_list,
        "up": up_list,
        "down": down_list
    },
    "device_info": {
        "kakou": kakou_data_result,
        "up": up_data_result,
        "down": down_data_result
    }
}
save_path = r'D:\GJ\project\SiChuan\NaQian\device_info-0714.json'
with open(save_path, "w", encoding="utf-8") as f:
    json.dump(save_data, f, ensure_ascii=False, indent=4)

a = {1: {'cameraIP': '10.144.32.81', 'preBaseStationID': nan, 'longitude': 105.51945434, 'latitude': 27.77443434,
         'PileNumber': 'K1727+208', 'distance': 0.0, 'direction': '上行', 'deviceType': '枪机', 'laneNumber': 2,
         'roadLimitSpeed': 80, 'splitMergeFlag': 0},
     2: {'cameraIP': '10.144.32.68', 'preBaseStationID': 1.0, 'longitude': 105.52745234, 'latitude': 27.77490935,
         'PileNumber': 'K1728+008', 'distance': 0.8, 'direction': '上行', 'deviceType': '枪机', 'laneNumber': 2,
         'roadLimitSpeed': 80, 'splitMergeFlag': 0}}
